package yoyo.secret.app.db;

import java.util.ArrayList;
import java.util.List;

import yoyo.secret.app.model.ClassIfication;
import yoyo.secret.app.model.UserInfo;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class DataBaseOperation extends SDCardSQLiteOpenHelper {

	private String TAG = DataBaseOperation.class.getName();

	private static final int DATABASE_VERSION = 4;
	private static String DATABASE_NAME = "yoyo.db";
	private static final String TABLE_NAME = "dictionary";
	private static final String CALSS_TABLE_NAME = "classification";
	// 分类表
	private static final String CLASSIFICATION_TABLE_CREATE = "CREATE TABLE " + CALSS_TABLE_NAME + " (" + Columns.CLASSIFICATION
			+ " varchar(50)," + " PRIMARY KEY(\"classification\"));";

	// 帐号表
	private static final String DICTIONARY_TABLE_CREATE = "CREATE TABLE " + TABLE_NAME + " (" + Columns._ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT," + Columns.ACCOUNT + " varchar(20)," + Columns.PASSWORD + " varchar(30) ,"
			+ Columns.CLASSIFICATION + " varchar(50)," + Columns.TYPE + " varchar(50)," + Columns.NAME + " varchar(50));";

	List<ClassIfication> dataClassIfications = null;
	List<UserInfo> userInfos = null;
	ContentValues values = null;

	public DataBaseOperation(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		dataClassIfications = new ArrayList<ClassIfication>();
		userInfos = new ArrayList<UserInfo>();
		values = new ContentValues();

	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(DICTIONARY_TABLE_CREATE);
		db.execSQL(CLASSIFICATION_TABLE_CREATE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.i(TAG, "oldVersion:" + oldVersion + "  newVersion:" + newVersion);
		db.execSQL("ALTER TABLE " + TABLE_NAME + " ADD " + Columns.NAME + " varchar(50);");
	}

	@Override
	public void onOpen(SQLiteDatabase db) {

		super.onOpen(db);
	}

	/**
	 * 获取所有分类
	 * 
	 * @return
	 */
	public List<ClassIfication> getClassificationList() {

		Cursor cur = getReadableDatabase().query(CALSS_TABLE_NAME, new String[] { Columns.CLASSIFICATION }, null, null, null, null, null);

		// final int id = cur.getColumnIndex(Columns._ID);
		final int classificationIndex = cur.getColumnIndex(Columns.CLASSIFICATION);

		ClassIfication classIfication = null;
		dataClassIfications.clear();
		Log.i(TAG, "idN:" + cur.getCount());
		if (cur.getCount() > 0) {
			while (cur.moveToNext()) {

				classIfication = new ClassIfication();

				// int idN = cur.getInt(id);
				String className = cur.getString(classificationIndex);

				// classIfication.id = idN;
				classIfication.className = className;
				// Log.i(TAG, "idN:" + 0 + "  classification:" + className);
				dataClassIfications.add(classIfication);

			}
		}
		cur.close();
		return dataClassIfications;
	}

	/**
	 * 添加一个分类
	 */
	public long addClassification(String name) {
		values.clear();
		values.put(Columns.CLASSIFICATION, name);

		SQLiteDatabase db = getWritableDatabase();
		long count = -1;

		try {
			db.beginTransaction();
			count = db.insert(CALSS_TABLE_NAME, null, values);
			db.setTransactionSuccessful();
		} catch (Exception e) {
		} finally {
			db.endTransaction();
		}

		return count;
	}

	public long delClassification(String name) {
		return getWritableDatabase().delete(CALSS_TABLE_NAME, Columns.CLASSIFICATION + "=?", new String[] { name });
	}

	/**
	 * 重命名分类名称
	 * 
	 * @param name
	 * @return
	 */
	public long editClassification(String name) {

		values.clear();
		values.put(Columns.CLASSIFICATION, name);
		SQLiteDatabase db = getWritableDatabase();
		db.beginTransaction();

		long count = getWritableDatabase().update(CALSS_TABLE_NAME, values, Columns.CLASSIFICATION + "=?", new String[] { name });

		if (count > 0) {
			count = getWritableDatabase().update(TABLE_NAME, values, Columns.CLASSIFICATION + "=?", new String[] { name });
		}
		if (count > 0) {
			db.endTransaction();
		}
		return count;
	}

	public int getUserInfoCount(String value) {
		Cursor cur = getReadableDatabase().query(TABLE_NAME, null, Columns.CLASSIFICATION + "=?", new String[] { value }, null, null, null);
		return cur.getCount();
	}

	/**
	 * 根据分类，获取当前分类下保存的内容
	 * 
	 * @return
	 */
	public List<UserInfo> getUserInfo(String value) {

		Cursor cur = getReadableDatabase().query(TABLE_NAME, null, Columns.CLASSIFICATION + "=?", new String[] { value }, null, null, null);

		final int id = cur.getColumnIndex(Columns._ID);
		final int nameIndex = cur.getColumnIndex(Columns.NAME);
		final int classificationIndex = cur.getColumnIndex(Columns.CLASSIFICATION);
		final int accountIndex = cur.getColumnIndex(Columns.ACCOUNT);
		final int passwordIndex = cur.getColumnIndex(Columns.PASSWORD);
		final int typeIndex = cur.getColumnIndex(Columns.TYPE);

		UserInfo userInfo = null;
		userInfos.clear();
		if (cur.getCount() > 0) {
			while (cur.moveToNext()) {

				userInfo = new UserInfo();

				userInfo.id = cur.getInt(id);
				userInfo.name = cur.getString(nameIndex);
				userInfo.classification = cur.getString(classificationIndex);
				userInfo.account = cur.getString(accountIndex);
				userInfo.password = cur.getString(passwordIndex);
				userInfo.type = cur.getString(typeIndex);

				userInfos.add(userInfo);

			}
		}
		cur.close();
		return userInfos;
	}

	/**
	 * 添加一个帐号
	 * 
	 * @param account
	 * @param password
	 * @param classString
	 * @param type
	 * @return
	 */
	public long addUserInfo(CharSequence account, CharSequence password, CharSequence classString, CharSequence type, CharSequence name) {

		values.clear();
		values.put(Columns.ACCOUNT, account.toString());
		values.put(Columns.PASSWORD, password.toString());
		values.put(Columns.CLASSIFICATION, classString.toString());
		values.put(Columns.NAME, name.toString());
		values.put(Columns.TYPE, type.toString());
		return getWritableDatabase().insert(TABLE_NAME, null, values);

	}

	public long editUserInfo(String id, String account, String password, String classString, String type) {
		values.clear();
		values.put(Columns.ACCOUNT, account);
		values.put(Columns.PASSWORD, password);
		values.put(Columns.CLASSIFICATION, classString);
		values.put(Columns.TYPE, type);
		return getWritableDatabase().update(TABLE_NAME, values, Columns._ID + "=?", new String[] { id });

	}

	public long delUserInfo(String id) {
		return getWritableDatabase().delete(TABLE_NAME, Columns._ID + "=?", new String[] { id });
	}
}
